package com.neo.service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.neo.util.DjsStringUtil;
import com.neo.util.RandomUserNameUtil;
import com.neo.util.ReadFromFile;


@Service
public class TransferUserService {

    protected static Logger logger= LoggerFactory.getLogger(TransferUserService.class); 
    
	@Autowired
    private JdbcTemplate jdbcTemplate;
	@Autowired
	private RandomUserNameUtil randomUserNameUtil;
	
	//CVS文件存储借款人信息
	public void insertInvUser() throws Exception {
		//List listCsv = ReadFromFile.readFileByLinesGbk(DjsStringUtil.IMPORT_FILE_PATH);
		List<String> listCsv = ReadFromFile.readFileByScanner(DjsStringUtil.IMPORT_FILE_PATH);
		
		int res=0;
		for (int i = 1; i < listCsv.size(); i++) {
			String str = listCsv.get(i);
			String[] s = str.split(",");
			String userName = randomUserNameUtil.getRandomUserName();
			String name = s[12];
			String idCardNo = s[13];
			String idCardType = "1";
			String mobileNo = s[14];
			String channel = "1";

			List<Map<String,Object>> bondProjects = jdbcTemplate.queryForList("select 1 from inv_user where MOBILE=?",new Object[]{mobileNo});
			if (bondProjects.size()==0) {
				String sql = "insert into inv_user (USERNAME,NAME,IDENTITY_NO,IDENTITY_TYPE,MOBILE,REGISTER_CHANNEL_NO) values(?,?,?,?,?,?)";
				jdbcTemplate.update(sql,userName,name,idCardNo,idCardType,mobileNo,channel);
				String sqlForAcc = "insert into jx_account_info (USERNAME,ID_TYPE,ID_NO,NAME,MOBILE) values(?,?,?,?,?)";
				int j = jdbcTemplate.update(sqlForAcc,userName,idCardType,idCardNo,name,mobileNo);
				res +=j;
				logger.info("结果:"+res+"=="+i);
			}
		}
		logger.info("结果文件数:"+res);
	}
	
	
	
	/**
	 * 批量更新
	 * @throws Exception
	 */
	public void insertInvUserBatch() throws Exception {
		//List listCsv = ReadFromFile.readFileByLinesGbk(DjsStringUtil.IMPORT_FILE_PATH);
		List<String> listCsv = ReadFromFile.readFileByScanner(DjsStringUtil.IMPORT_FILE_PATH);
		Map<String,String[]> map = new HashMap();
		int res=0;
		int error=0;
		//listCsv = listCsv.subList(0, 20);
		logger.info("listCsv size=="+listCsv.size());
		for (int i = 1; i < listCsv.size(); i++) {
			String str = listCsv.get(i);
			String[] s = str.split(",");
			String name = s[12];
			String idCardNo = s[13];
			String mobileNo = s[14];
			
			if(name.indexOf("*")>=0 || idCardNo.indexOf("*")>=0 || mobileNo.indexOf("*")>=0) {
				error++;
				continue;
			}
			map.put(mobileNo, s);
			logger.info("1=="+i);
		}
		logger.info("error=="+error);
		logger.info("2=="+map.size());
		List<String[]> newList = new ArrayList();
		for (String[] str: map.values()) {
			newList.add(str);
		}

		//newList = newList.subList(5000, newList.size());
		logger.info("3=="+newList.size());
		for (int i = 0; i < newList.size(); i++) {
			String[] s = newList.get(i);
			//s[0] = userName;//temp add 
			String name = s[12];
			String idCardNo = s[13];
			String mobileNo = s[14];
			String idCardType = "1";
			String channel = "1";
			List<Map<String,Object>> bondProjects = jdbcTemplate.queryForList("select 1 from inv_user where MOBILE=?",new Object[]{mobileNo});
			if (bondProjects.size()==0) {
				String userName = randomUserNameUtil.getRandomUserName();
				String sql = "insert into inv_user (USERNAME,NAME,IDENTITY_NO,IDENTITY_TYPE,MOBILE,REGISTER_CHANNEL_NO) values(?,?,?,?,?,?)";
				jdbcTemplate.update(sql,userName,name,idCardNo,idCardType,mobileNo,channel);
				String sqlForAcc = "insert into jx_account_info (USERNAME,ID_TYPE,ID_NO,NAME,MOBILE) values(?,?,?,?,?)";
				int j = jdbcTemplate.update(sqlForAcc,userName,idCardType,idCardNo,name,mobileNo);
				res +=j;
			}
			logger.info("结果:"+res+"/"+(i+1));
		}
		//logger.info("=="+newList.size());
		//insertUserBatch(newList);
		logger.info("结果文件数:"+res);
	}
	private void insertUserBatch(List<String[]> list) {
		String updateSql2 = "insert into inv_user (USERNAME,NAME,IDENTITY_NO,IDENTITY_TYPE,MOBILE,REGISTER_CHANNEL_NO) values(?,?,?,?,?,?)";
		jdbcTemplate.batchUpdate(updateSql2, new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement preStatement, int i) throws SQLException {
				String[] s = list.get(i);
				String userName = s[0];
				String name = s[12];
				String idCardNo = s[13];
				String idCardType = "1";
				String mobileNo = s[14];
				String channel = "1";
				preStatement.setString(1, userName);
				preStatement.setString(2, name);
				preStatement.setString(3, idCardNo);
				preStatement.setString(4, idCardType);
				preStatement.setString(5, mobileNo);
				preStatement.setString(6, channel);
			}
			@Override
			public int getBatchSize() {
				return list.size();//这个必须是list.size 不能改
			}
		});
	}
	//借款人导入用户表
	//SELECT * FROM inv_user WHERE identity_no IN (SELECT ID_CARD FROM inv_bond_project)
	//SELECT * FROM jx_account_info WHERE ID_NO IN (SELECT ID_CARD FROM inv_bond_project)

	public void bondProjectToUserAccount() throws Exception{
		String select_sql = "SELECT IFNULL(OWN_BOND_NAME,'') AS OWN_BOND_NAME, IFNULL(ID_CARD,'') AS ID_CARD FROM inv_bond_project";
		
		List<Map<String,Object>> bondProjects = jdbcTemplate.queryForList(select_sql,new Object[]{});
		
		if(bondProjects != null && bondProjects.size() > 0){
			for(Map<String,Object> map : bondProjects){
				String userName = randomUserNameUtil.getRandomUserName();
				StringBuilder insertSql = new StringBuilder("INSERT INTO inv_user(");
				insertSql.append("USERNAME,NAME,IDENTITY_NO,IDENTITY_TYPE,REGISTER_CHANNEL_NO) VALUES (");
				insertSql.append("'" + userName + "'," );
				insertSql.append("'" + map.get("OWN_BOND_NAME") + "',");
				insertSql.append("'" + map.get("ID_CARD") + "',");
				insertSql.append("'" + 1 + "',");
				insertSql.append("'" + 1 + "'");
				insertSql.append(")");
				int result = jdbcTemplate.update(insertSql.toString());
				
				StringBuilder insertJxSql = new StringBuilder("INSERT INTO jx_account_info (USERNAME,ID_TYPE,ID_NO,NAME) VALUES(");
				insertJxSql.append("'" + userName + "',");
				insertJxSql.append("'" + 1 + "',");
				insertJxSql.append("'" + map.get("ID_CARD") + "',");
				insertJxSql.append("'" + map.get("OWN_BOND_NAME") + "'");
				insertJxSql.append(")");
				jdbcTemplate.update(insertJxSql.toString());
			}
		}
		
	}
	public static void main(String[] args) {
		 Map<String, String> map = new HashMap<String, String>();
		  map.put("1", "value1");
		  map.put("2", "value2");
		  map.put("3", "value3");

		 for (String str: map.values()) {
			 System.out.println(str);
			}
		  //第一种：普遍使用，二次取值
		  System.out.println("通过Map.keySet遍历key和value：");
		  for (String key : map.keySet()) {
		   System.out.println("key= "+ key + " and value= " + map.get(key));
		  }
	}
}
